package com.eascs.bankloan.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.bankloan.dao.O2oBankLoanDao;
import com.eascs.bankloan.dao.OaBankLoanDao;
import com.eascs.bankloan.entity.O2oBankLoanDataEntity;
import com.eascs.bankloan.entity.O2oBankLoanEntity;
import com.eascs.bankloan.entity.O2oFormEntity;
import com.eascs.bankloan.entity.O2oFromOaBankLoanEntity;
import com.eascs.bankloan.entity.O2oToBankLoanEntity;
import com.eascs.bankloan.entity.OaBankLoanEntity;
import com.eascs.common.util.StringUtil;
import com.eascs.loan.entity.O2oBankLoanData;

@Service
public class O2oBankLoanService {

	@Autowired
	O2oBankLoanDao loanapplyDao;
	@Autowired
	OaBankLoanDao oaBankLoanDao;

	/**
	 * 分页查询申报中的贷款数据
	 * 
	 * @return
	 */
	public PageResult<Object[]> findByPage() {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql = new StringBuffer();
		querySql.append(" SELECT OL.BILL_CODE,OL.BANK_LOAN_NAME,OLU.USER_FULL_NAME, ");
		querySql.append(" OL.EA_MOUNT, OL.LOAN_TYPE, ");
		querySql.append(" CONCAT(OL.LTD_CODE,'-',OL.LTD_NAME) AS LTD_NAME ");
		querySql.append(" FROM O2O_LOANAPPLY OL JOIN O2O_LOAN_USER OLU ON OL.LOAN_ID = OLU.LOAN_ID ");
		querySql.append(" WHERE OL.BANK_LOAN_NAME IS NOT NULL AND OL.WF_TYPE = 'S' AND OL.ISLOCK = 'N' AND OL.LOAN_FLG = 'N' AND OL.IS_BANK_LOAN_AMOUNT = 'Y' GROUP BY OL.BILL_CODE");
		sqlPageQuery.setQuerySql(querySql.toString());
		return loanapplyDao.findDataWithPage(sqlPageQuery);
	}

	/**
	 * 更新主表贷款信息
	 * 
	 * @param loanEntity
	 * @return
	 */
	public int update(O2oBankLoanEntity loanEntity) {
		return loanapplyDao.updateById(loanEntity);
	}

	/**
	 * 查询
	 * 
	 * @param billCode
	 * @return
	 */
	public O2oBankLoanEntity getById(String billCode) {
		return loanapplyDao.getById(O2oBankLoanEntity.class, billCode);
	}

	/**
	 * 查询主表放款信息
	 * 
	 * @param billCode
	 * @return
	 */
	public O2oBankLoanDataEntity getEntity(String billCode) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT OL.BILL_CODE,OL.LOANA_MOUNT,OL.EA_MOUNT,OL.BANK_LOAN_NAME,OL.PAYMENT_TIME,OL.LOANRATE,OL.LOAN_TERM,OLU.USER_FULL_NAME,OLU.DOCUMENT_NO ");
		sql.append(" FROM O2O_LOANAPPLY OL JOIN O2O_LOAN_USER OLU ON OL.LOAN_ID = OLU.LOAN_ID ");
		sql.append(" WHERE OL.BILL_CODE IS NOT NULL AND OL.WF_TYPE = 'O' AND OL.ISLOCK = 'N' AND LOAN_FLG = 'Y' AND OL.BILL_CODE = ? ");
		return loanapplyDao.findEntity(sql.toString(), O2oBankLoanDataEntity.class, billCode);
	}

	/**
	 * 向放款表中插入数据
	 * 
	 * @param bankLoanData
	 * @return
	 */
	public int insert(O2oBankLoanData bankLoanData) {
		return loanapplyDao.insert(bankLoanData);
	}

	public OaBankLoanEntity findByOaEntity(String billCode) {
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT OLL.LOANAPPLYNO,OLL.BILLCODE,ORR.BANKTYPE,ORR.CUSTOMERNAME,HU.UNITCODE||'-'||HU.NAMECN AS NAMECN, ");
		querySql.append("OL.LOANERNAME,OL.IDCARD,ORR.BANKLOANAMOUNT,OLL.LOAN_TYPE,ORR.LOANAMOUNT,ORR.INTERESTDATE,ORR.LOADDATE ");
		querySql.append(",ORR.CREATOR,ORR.CREATORNAME,ORR.RATE,ORR.LOANTERM ");
		querySql.append("FROM EADB.O2O_REFUNDCONTRACT ORCT JOIN EADB.O2O_REFUND ORR ON ORR.REFUNDNO = ORCT.REFUNDNO ");
		querySql.append("JOIN EADB.O2O_LOANER OL ON OL.LOANAPPLYNO=ORR.LOANAPPLYNO ");
		querySql.append("JOIN EADB.O2O_LOANAPPLY OLL ON OLL.LOANAPPLYNO = OL.LOANAPPLYNO ");
		querySql.append("JOIN EADB.HR_UNITS HU ON  HU.UNITNO = ORR.LTD  ");
		querySql.append("WHERE ORCT.BILLSTATUS='RUN' AND ORCT.ISLOCK='N' AND ORCT.ISFROM='BAK' AND OLL.BILLCODE = ? ");
		return oaBankLoanDao.findEntity(querySql.toString(), OaBankLoanEntity.class, billCode);
	}
	
	public O2oToBankLoanEntity findByO2oToEntity(String billCode){
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT  OL.LOANAPPLY_NO,OL.BILL_CODE,OL.BANK_TYPE,CONCAT(OL.CUSTOMER_CODE,'-',OL.CUSTOME_RNAME) AS CUSTOME_RNAME ");
		querySql.append(",OL.LTD_NAME,OLU.USER_FULL_NAME,OLU.DOCUMENT_NO,OL.EA_MOUNT,OL.LOAN_TYPE ");
		querySql.append(",OL.LOANA_MOUNT,OL.END_DATE,OL.PAYMENT_TIME,OL.CREAT_RO,OL.SUBMIT_RO,OL.LOANRATE,OL.LOAN_TERM ");
		querySql.append("FROM O2O_LOANAPPLY OL JOIN O2O_LOAN_USER OLU ON OL.LOANAPPLY_NO = OLU.LOANAPPLY_NO AND OL.LOAN_ID=OLU.LOAN_ID WHERE OL.BILL_CODE = ?");
		return loanapplyDao.findEntity(querySql.toString(), O2oToBankLoanEntity.class, billCode);
	}

	public PageResult<Object[]> findByOaPage(O2oFormEntity entity) {
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, entity.getPageSize(), entity.getPageIndex());
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT OLL.BILLCODE,ORR.BANKTYPE,OL.LOANERNAME,NVL(ORR.BANKLOANAMOUNT,0) AS BANKLOANAMOUNT,OLL.LOAN_TYPE,HU.UNITCODE||'-'||HU.NAMECN AS NAMECN ");
		querySql.append("FROM EADB.O2O_REFUNDCONTRACT ORCT JOIN EADB.O2O_REFUND ORR ON ORR.REFUNDNO = ORCT.REFUNDNO ");
		querySql.append("JOIN EADB.O2O_LOANER OL ON OL.LOANAPPLYNO=ORR.LOANAPPLYNO ");
		querySql.append("JOIN EADB.O2O_LOANAPPLY OLL ON OLL.LOANAPPLYNO = OL.LOANAPPLYNO ");
		querySql.append("JOIN EADB.HR_UNITS HU ON  HU.UNITNO = ORR.LTD  ");
		querySql.append("WHERE ORCT.BILLSTATUS='RUN' AND ORCT.ISLOCK='N' AND ORCT.ISFROM='BAK' AND ORR.LOADDATE IS NULL AND ORR.billstatus <> 'REJ' ");

		SqlPageQuery<Object[]> sqlO2oPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		String sql = "SELECT OLO.BILL_CODE FROM O2O_LOAN_OAOVERDUE OLO";
		sqlO2oPageQuery.setQuerySql(sql);
		PageResult<Object[]> pageResult = loanapplyDao.findDataWithPage(sqlO2oPageQuery);
		if (null != pageResult && pageResult.getData().size() > 0) {
			StringBuffer dataP = new StringBuffer();
			for (int i = 0; i < pageResult.getData().size(); i++) {
				dataP.append("'" + pageResult.getData().get(i)[0] + "',");
			}
			String d = "";
			if (dataP.length() > 0) {
				d = dataP.substring(0, dataP.length() - 1);
			}
			if (!StringUtil.isEmpty(d)) {
				querySql.append(" AND OLL.BILLCODE NOT IN ( ");
				querySql.append(d);
				querySql.append(" )");
			}
		}
		querySql.append(" ORDER BY ORR.BANKTYPE,ORR.LOANERNAME ASC ");
		sqlPageQuery.setQuerySql(querySql.toString());
		return oaBankLoanDao.findDataWithPage(sqlPageQuery);
	}

	public int insert(O2oFromOaBankLoanEntity entity) {
		return loanapplyDao.insert(entity);
	}
	
	public int update(O2oFromOaBankLoanEntity entity) {
		return loanapplyDao.insert(entity);
	}
	
	
	public PageResult<Object[]> findByOaAndO2oPage(String userName){
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		PageResult<Object[]> oaAndO2o = new PageResult<>();
		
		StringBuffer querySql = new StringBuffer();
		querySql.append("SELECT OLL.BILLCODE,ORR.BANKTYPE,OL.LOANERNAME,OL.IDCARD,NVL(OLL.LOANAMOUNT,0) AS LOANAMOUNT,");
		querySql.append("NVL(ORR.BANKLOANAMOUNT,0) AS BANKLOANAMOUNT,OLL.LOAN_TYPE,HU.UNITCODE||'-'||HU.NAMECN AS NAMECN ");
		querySql.append("FROM EADB.O2O_REFUNDCONTRACT ORCT JOIN EADB.O2O_REFUND ORR ON ORR.REFUNDNO = ORCT.REFUNDNO ");
		querySql.append("JOIN EADB.O2O_LOANER OL ON OL.LOANAPPLYNO=ORR.LOANAPPLYNO ");
		querySql.append("JOIN EADB.O2O_LOANAPPLY OLL ON OLL.LOANAPPLYNO = OL.LOANAPPLYNO ");
		querySql.append("JOIN EADB.HR_UNITS HU ON  HU.UNITNO = ORR.LTD  ");
		querySql.append("WHERE ORCT.BILLSTATUS='RUN' AND ORCT.ISLOCK='N' AND ORCT.ISFROM='BAK' AND ORR.LOADDATE IS NULL AND ORR.billstatus <> 'REJ' ");
		
		
		SqlPageQuery<Object[]> sqlPageQuery2 = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql2 = new StringBuffer();
		querySql2.append(" SELECT OL.BILL_CODE,OL.BANK_LOAN_NAME,OLU.USER_FULL_NAME,OLU.DOCUMENT_NO, ");
		querySql2.append(" OL.EA_MOUNT,OL.BANK_AMOUNT, OL.LOAN_TYPE, ");
		querySql2.append(" CONCAT(OL.LTD_CODE,'-',OL.LTD_NAME) AS LTD_NAME ");
		querySql2.append(" FROM O2O_LOANAPPLY OL JOIN O2O_LOAN_USER OLU ON OL.LOANAPPLY_NO = OLU.LOANAPPLY_NO AND OL.LOAN_ID=OLU.LOAN_ID");
		querySql2.append(" WHERE OL.BANK_LOAN_NAME IS NOT NULL AND OL.WF_TYPE = 'S' AND OL.ISLOCK = 'N' AND OL.LOAN_FLG = 'N' AND OL.IS_BANK_LOAN_AMOUNT = 'Y' ");
		
		SqlPageQuery<Object[]> sqlO2oPageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		String sql = "SELECT OLO.BILL_CODE FROM O2O_LOAN_OAOVERDUE OLO WHERE OLO.LOAN_STATUS = ?";
		sqlO2oPageQuery.addParam(StatusContexts.O2O_YES);
		sqlO2oPageQuery.setQuerySql(sql);
		PageResult<Object[]> pageResult = loanapplyDao.findDataWithPage(sqlO2oPageQuery);
		if (null != pageResult && pageResult.getData().size() > 0) {
			StringBuffer dataP = new StringBuffer();
			for (int i = 0; i < pageResult.getData().size(); i++) {
				dataP.append("'" + pageResult.getData().get(i)[0] + "',");
			}
			String d = "";
			if (dataP.length() > 0) {
				d = dataP.substring(0, dataP.length() - 1);
			}
			if (!StringUtil.isEmpty(d)) {
				querySql.append(" AND OLL.BILLCODE NOT IN ( ");
				querySql.append(d);
				querySql.append(" )");
				querySql2.append(" AND OL.BILL_CODE NOT IN ( ");
				querySql2.append(d);
				querySql2.append(" )");
			}
		}
		if(!userName.trim().isEmpty()){
			querySql.append(" AND OL.LOANERNAME IN ( ");
			querySql.append(userName);
			querySql.append(" ) ");
			
			querySql2.append(" AND OLU.USER_FULL_NAME IN ( ");
			querySql2.append(userName);
			querySql2.append(" ) ");
		}
		querySql.append(" ORDER BY ORR.BANKTYPE,ORR.LOANERNAME ASC ");
		querySql2.append(" GROUP BY OL.BILL_CODE ");
		sqlPageQuery.setQuerySql(querySql.toString());
		sqlPageQuery2.setQuerySql(querySql2.toString());
		
		PageResult<Object[]> o2o = loanapplyDao.findDataWithPage(sqlPageQuery2);
		PageResult<Object[]> oa = oaBankLoanDao.findDataWithPage(sqlPageQuery);
		
		List<Object[]> oalist = oa.getData();
		oalist.addAll(o2o.getData());
		oaAndO2o.setData(oalist);
		oaAndO2o.setDbRowCount(oa.getDbRowCount()+o2o.getDbRowCount());
		oaAndO2o.setPageIndex(0);
		oaAndO2o.setPageSize(Integer.MAX_VALUE);
		oaAndO2o.setPageTotal(oa.getPageTotal()+o2o.getPageTotal());
		oaAndO2o.setReturnDataSize(oa.getReturnDataSize()+o2o.getReturnDataSize());
		return oaAndO2o;
	}
	
	public int updateOaOverdue(String billCode){
		String sql = "UPDATE O2O_LOAN_OAOVERDUE OLO SET OLO.LOAN_STATUS = ? WHERE OLO.BILL_CODE = ?";
		return loanapplyDao.execute(sql, StatusContexts.O2O_YES, billCode);
	}
	
	public PageResult<Object[]> findByOaO2oPage(String billCode){
		SqlPageQuery<Object[]> sqlPageQuery2 = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer querySql2 = new StringBuffer();
		querySql2.append("SELECT OLO.BILL_CODE,OLO.LOAN_BANK,OLO.LOAN_USERNAME,OLO.LOAN_BANK_AMOUNT,OLO.LOAN_TYPE,");
		querySql2.append(" OLO.LTD_NAME,OLO.LOAN_ACTUAL_TIME FROM O2O_LOAN_OAOVERDUE OLO WHERE OLO.BILL_CODE IN ( ");
		querySql2.append(billCode);
		querySql2.append(" )");
		sqlPageQuery2.setQuerySql(querySql2.toString());
		return loanapplyDao.findDataWithPage(sqlPageQuery2);
	}
	
	public int getOaO2oOverdueCount(String billCode){
		String sql = "SELECT COUNT(1) FROM O2O_LOAN_OAOVERDUE OLO WHERE OLO.BILL_CODE = ?";
		return loanapplyDao.findInteger(sql, billCode);
	}
	
	public String getLoanapplyNo(String billCode){
		String sql = "SELECT OL.LOANAPPLY_NO FROM O2O_LOANAPPLY OL WHERE OL.BILL_CODE=?";
		return loanapplyDao.findString(sql, billCode);
	}
}
